

Table of Content
Contents
 




 !
" ##$!
"#$!
""$%
& '() "
*((+**,+* "-
*((+.'("-
*((+.'(*.,/01,0*&%
*0(2) &
*((+**03+.'(&"
*$,45.,$.6457',+*0,&&
*+0',*45/.8945.,'$457',+*0,&:
*;(<03(.*0&
*,.,,0*,03(.*0&-
*2(*((,03(.*0&=
*.. :%
*90'32)*.*($(,*:
*.8,9+.'(:"
*',0,:&
**0(30+('(::
: >0, ::
,,(>0, :
(7*>0, :
9*>0, :!
70'*(>0, :-
(7<>0, :=
 .*.2.( %
*+(.*(.*.2.(*.*($(,*%
*03.*.2.(*.*($(,*%
*+(.*(*.2(
*03*.2(*.*($(,*"
*,(*,*0*.*($(,*&
*,'8.'( :
*'3.*(*.*($(,*
*((*(*.*($(,*
*.*(*.2(*.*($(,*!
 .*(*.2(<.+0'$,,(6*,9*.2(!
" .*(*.2(?$07)@.*(+0'$,-
& .*(*.2(<03+0'$,-
 *+0,*.,*=
,0*,'+0,*.,*%
','(+0,*.,*
03.','(+0,*.,*&
3$.);()+0,*.,*:
033$.);()+0,*.,*
70(9,;()+0,*.,*!
03.70(9,;()+0,*.,*=
+(+;+0,*.,* =
03.+(+;+0,*.,*!
(7.'*+0,*.,*!
03.(7.'*+0,*.,*!&
! +(.*(,(6*.*($(,*!:
03,(6*.*($(,*!
- 8(*.*($(,* !
**+(+;03*0, !!
((*,90,*0.8(!-
033,98( !-
= .A$ !=
= $3!=
= +#3!=
=" (6(+'*0,07*0(30+('(-%
=& 03*(*0(30+('(-
=: *0(30+('(3..$(*(-"
= *0(30+('(8..2(-
=" +0,*0,.*.*($(,*-
=" 7<*(,*.*($(,*-!
="" 7<*(,<((*.*($(,*--
="& 7*(,((7((*.*($(,*-=
=& +.(*.*($(,* =%
=&  B+.( =%
=&" +.( ="
=: 003*.*($(,*=
= (003*.*($(,*=
= (3(.*003*.*($(,*=-
=! +'0==
1. Introduction to SQL
##/
#/ B/A# 
History of SQL
* 4  ##5  C   
=!%  2$    2  
+ *##/D /
#B#(#7D*EBB/
=!%/.$#2D
BB/*BB
B 
+ 2 B*#
A#A B
2$E # 2$ D F F  E 
A/A/##
  A B  =!=/  B   
 /     0/  
A##08"
 /. ,4.,5
 0#GA  
 ##    ##   
  HA ##
/ B#  I.,
BBAA
What is Database?
.<.
   B      B/
/B/#G
 ##/#/A# $
   #  #  
42$5
Relational Database
4
 5     JJ # 
   JJ / /
  !""#/$//

SQL and Relational Databases
. 
K###%  
*L
& 


+
How to run SQL Query on the local system
*K /
$) A A#AB
BB
2. Downloading and Installing MySQL
2.1 Downloading MySQL
%'(0B9#B ) +D
  ) 
%*(+D #
%+(+D$$,   ) 
&-
%.(0D 
%/(+D ) 
 (0# B/<D
 
2.2. Installation of MySQL
%'( 01.BB
%*(.D#BB#D


%+(+#BBD,M
%.(+D,M
%/(+D)

%2(+D(M
%3(.(M/D,M
%4(+D,M
%5(+D,M
%'6(AD,M
%''(+D,M
%'*(+B
%'+(+D.'#A B
%'.(.# BD0;

%'/(.#D,M
%'2(+D,M
%'3(+D(M
%'4(.+D#M/D7
%'5(+D,M
%*6(+D7
%*'(+D,M
%**(+DB+D,M
%*+(+D(M
%*.(.D#(M
%*/(+D,M
%*2(+D7
%*3(.$/
B
$
$D2
$D2A
$
%*4(+D20B$
+ 
%*5(0B$+ <+
B
%*5(.#B/$#
$
%+6(* <$N
B7 )8
%+'LAHB#
9

3. SQL QUERY
.          C
*45*
/M$
B # 
,L  D   <A 4#/  
((+*5
*M#### 
M D B(#
;((+*/'3.*(/((/0(2)(*+
7 BBBL
 <<
" <<
& 'BM#<<&$
:  A?
* B       4+/ /
'B/5
The SQL SELECT QUERY
*((+* B   

*#ML
((+*CO /O 
 70$ N
:%L   CO   O   
 
* /;
((+*;
 70$ N
The SQL SELECT DISTINCT
*((+**,+* IA
((+**,+*CO 
70$ N
The SQL WHERE CLAUSE
*((C 
*((M
BC
<7)=7:#
K#K:#4 
 K5.>
K
((+*CO 70$ 
((O PQBRN
<7)7>
((+*CO /O 70$ ((
AP%N
% )7
? (K
- 9
@ 
-? 9K
@? K
@- ,K4SP5
A<
2#
B B
 B BBA 
The SQL WHERE CLAUSE WITH AND, OR & NOT
$<C)7$L
((+*CO / /O
70$ 
((CO PQ.,R.,O PQ*R
$<C)7(
'3.*( 
(*CO PQH#R
((O PQ 9()RN
$<C)7(
O/CO /O / /O
70$ 
((,0*OP"N
The SQL ORDER BY
0BA 4#
#5
A 
((+*CO /O / 
70$ 
0(2)CO (+N
A$ 
((+*CO /O / 
70$ 
0(2)CO .+N
The SQL SELECT TOP CLAUSE
*& B 
   * ((+* *03    #  
## 
 BB 
 L, BB((+**03
$BB$*  
/00,'$
:(
((+*CO /O / 
70$ ((CO PQ.'*,R
$*"%N
The SQL MIN() AND MAX() FUNCTION
*$,45 A
  *$.645
#A  
:
((+*$,4O5
70$ N
$D:
((+*$.64O5
70$ N

The SQL COUNT(), AVG() AND SUM() FUNCTION
*#A  
BC.$E,
A#A  *
   
:
((+*+0',*4 5
70$ N
$E,:
((+*.894A5
70$ N
:
((+*'$4A5
70$ 
The SQL LIKE-OPERATOR
*BB((C
BCB*
H;(BL
F<BG// B
9<B#
 L$.D4T5BB#
4U5K D45B4O5
*QURQOR 
B:
((+* / "/
70$O 
(( ;(BN
  !7 )7>9
)7GG8
((+*T70$ 
((CO ;(EUEN
  !7 )7>9
)7GG(
((+*T70$ 
((CO ;(EU(EN
  !7 )7>97
7#G G%  8
((+*T70$ 
((CO ;(EUUEN
  !7 )7>97
)7GG)7G G(
((+*T70$ 
((CO ;(EUEN
  !7 )7>97
)7GG:77(
((+*T70$ 
((CO ;(EOOUEN
The SQL IN AND NOT IN OPERATORS
*BB BA
((*,BA

:
((+* O 45
70$O 
(( O ,4A/A"/5N
(
((+* O 45
70$O 
(( O ,4((+**.*($(,*5N
7  ! )7  9
'=*=+(
((+*T70$ 
(( O,4/"/&5N
7  ! )7  9
'=*=+(
((+*T70$ 
(( O,0*,4/"/&5N
The SQL BETWEEN OPERATOR
*A<BAA#A#
*AM/ /*A<
BAL#A
A<:
((+* O 45
70$O 
(( O 2(*((,A.,A"N
7 ! 7 )7 9
)' *68
((+*T70$ (( O2(*((,
.,"%N
7 ! 7 )7
 9= )' /368
((+*T70$ (( O,0*
2(*((,.,!%N
The SQL ALIAS
.#AD  /
 B . D   

$ :
((+*CO .C/O .
70$ N
) = ! 7>9 
! 79 (
$:
((+*CO /O 
70$ .
! 7 
The SQL GROUP BY STATEMENT
*,&AH#B .
 A 7M B/C 
 /* ,&AH 
###D4+0',*/$.6/$,/'$/.895#B
<  
,&AH:
((+* O 45
70$O 
((
90'32) O 45
0(2) O 45N
 7 !# 0# 
((+*+0',*4 O570$ 90'32)
A
The SQL HAVING CLAUSE
*C$E,((D
###
C$E,:
((+* O 45
70$O 
((
90'32) O 45
.8,9
0(2) O 45N
7 ! )777  
728
((+*T #B45A#
4#5VN
The SQL UNION
*',0,B <
 ((+* (((+* 
 ',0,  A         *
 ((+*  
* A B
7
 O 45 
',0,
 O 45 "N
$
*',0,BIA*
BA/',0,.B
((+* O 4570$
',0,.
((+* O 4570$"N
 L*  BK
  C((+* ',0,
The SQL STORED PROCEDURE
<7 & I
*   %   BB  K   
AKAA#/
KAA#/
DBBM'B
B BB
B A#A
 & :
+(.*(30+('(BO 
.
KO 
90N
: & 
(6(+BO N
4. SQL JOIN
*>BA#  
* #B D
#D
Type of Join
INNER JOIN
*  J    B     
#A7M B/#
    A  A #   
A ',,(>0,/
         #A


7#,,(>0,
* ,,( >0, D    A #
A
J:
((+* O 45
70$
,,(>0,"
0, O P" O N
((+* O/ / OB/
OB70$
,,(>0,0,OPO
LEFT JOIN
*KJ 45
  "4#5*B
,' # 

>
KJ:
((+* O 45
70$
(7*>0,"
0, O P" O N
((+* O/ / OB/
OB70$
(7*>0,0,OPO
RIGHT JOIN
*9*>0,BB(7*>0,*9*>0,B
      "4# 5 A   
#A45
  A      4 5/ ,' 


9*>0,
,CJ:
((+* O 45
70$
9* >0, " 0,  O  P
" O N
((+* O/ / OB/
OB70$
9*>0,0,OPO
Full OUTER JOIN
*7'0'*(>0,D
 45#4"5
 L7'0'*(>0,BA#<
S

7>
*BL7'0'*(>0,7'>0, 
KJ:
((+* O 45
70$
7'0'*(>0,"
0,  O  P " O  ((
N
 L$BB7>/B
H#HBD 
((+*T70$
(7*>0,"0,P"
',0,
((+*T70$
9*>0,"0,P"

SELF-JOIN
.<>0,#H/H
!0J:
((+* O 45
70$*/*"
((N
5. SQL DATABASE
The SQL CREATE DATABASE STATEMENT
*+(.*(.*.2.( 

:
+(.*(.*.2.(O N
L#
+(.*(N

,/RD$#7 )
K
N

The SQL DROP DATABASE STATEMENT
*03.*.2.( BM#

:
03.*.2.(O N
L %7 %
8
03N

,/RD$#7 )
KBB#
0N

*45BB
The SQL CREATE TABLE
*+(.*(*.2( 

:
+(.*(*.2(O 4
 OB/
 "OB/
 &OB/

5N
* / "/W/B  
*BB BCB
 4#/A/#//5
L 
+(.*( *.2(  4 #/ CO  A4%5/
O  A4%5/  A4%5/ 
A45/BA455N
 7177 !7=98
(+ N

The SQL DROP TABLE STATEMENT
*03*.2( BM#

03*.2( N

*   BB  #  K B 
O  .   /     M 
BB
,## #
A
The SQL INSERT INTO STATEMENT
*,(*,*0 

M
,(*,*0 *C
B  A
L
,(*,*0 4/CO /
O ///B58.'(4"/Q.R/Q*HR/
Q$MR/Q$MR/45<:!"=5N
#A /
RBB  
KA/A 
 
7M) !  )L
,(*,*0 
8.'(4&/Q./Q$/Q$MR/Q$MR/45<
&=&"5N

 A      /    
7/A
B
((+*T70$ N
The SQL NULL VALUES
<7EI
*C,'ACAC
B/B#
AC*/CA,'
A
 L.,'A GA/
CB*C,'A
D#S
7#
,(* ,*0   8.'(4/ Q8R/ Q.R/
QR/,'/Q4!5<""R5
./,'A
C ) 71! EI
*,'A
B
:
((+*T
70$ ((,'N
:
((+*T70$ 
((,0*,'N
A A4MB,
A5
The SQL UPDATE STATEMENT
*'3.*(  M#

&$:
'3.*( 
(*PQ$MR((PN
ABA$M
The SQL DELETE STATEMENT
*((*( M#

:
((*(70$ ((PN
A/P
The SQL ALTER TABLE STATEMENT
*.*(*.2( / /
 M#.
BA
5.1.1.ALTER TABLE - ADD COLUMN IN EXISTING TABLE
* /K
.*(*.2( 
. A4"5N
5.1.2.ALTER TABLE – MODIFY/ALTER COLUMN
*#B A/
#ML
.*(*.2( .+0'$,N

A#B2
#B 
.*(*.2( $07)N

5.1.3.ALTER TABLE - DROP COLUMN
*BC /#M
4  E#
 5L
:(
.*(*.2( 
03+0'$, N
6. The SQL CONSTRAINTS
*+BC
+(.*(*.2( /
.*(*.2( 
:(
+(.*(*.2(O 4
  B/
  "B/
  &B/
 
5N
 
B
+ B
#          
  /      A  
    /    
+     A   A +  A
BB /<ABB

7   
$
&
,, ( A,'
A
'K (A 
K
3 ;  ,0*,'','(
'KC
7#; 'K C  @  

+D DA 
BC
 #AA 
ABC
M 'A 
KD
NOT NULL CONSTRAINTS
*,0*,' ,0*B,'
A *  B C    A/ 
          
B#AC
L2/ ,'A
  
* #     FF/ F7O F 
FO F    ,0* B ,' A  
FFL
:%
+(.*(*.2(4
 ,0*,'/
CO A4"5,0*,'/
 O A4"5,0*,'/
 #
5N
  A /   BC  / CO / 
O #
 $
* D,0*,'F#F 
FF/#L
:%(
.*(*.2(
$07)#,0*,'N

A/BC/CO /O /
#
SQL UNIQUE CONSTRAINT
*A 
','(3$.);()BA
# M  #B .
3$.) ;() /  /  ','( 
A/A ','(B/
3$.);()B
  7GG )7 7
G% G
+(.*(*.2(B4
 ,0*,'/
 O A4"5,0*,'/
 CO A4"5/
 #/
 ','(45
5N
ABBK//#
B D
    7 G>9G  
)77G% G:8
.*(*.2(B
.','(4CO 5N

,  A  K 4  CO 5  
B
*    ','( /   C ','(
 B /#ML
.*(*.2(B
.+0,*.,*'+OB','(4#/O 5N

#O AK
DROP A UNIQUE CONSTRAINT
*B','(/K
.*(*.2(B
03,(6'+O3N
.B*K
4'+O35BB
SQL PRIMARY KEY CONSTRAINTS
* 3$.) ;()  K C   
00,(B DA.
//B D# B
 4C53 D','(A/
A
+(.*( *.2( B4  ,0* ,'/ O 
A4"5 ,0* ,'/ CO  A4"5/ # /
3$.);()455N

* #3$.);()/C#
3$.) ;()   B  /   
M
+(.*(*.2(B4
 ,0*,'/
 O A4"5,0*,'/
 CO A4"5/
 #/
 +0,*.,*3;OB3$.);()4/O 5
5N

 L   M B/    0,( 3$.) ;() 
3;O3.8.'(B D B)
 4XO 5
&$HBH $$A
+3$.);() O FF
 O 45   /   #
L
.*(*.2(
.3$.);()45N
A#B DYZ

. #3$.);()/C#
3$.);() B /KL
.*(*.2(
.+0,*.,*3;O3$.);()4/CO 5N

DROP PRIMARY KEY CONSTRAINTS
*B3$.);() /
L
.*(*.2(
033$.);()N
. /B D
BB 
SQL FOREIGN KEY CONSTRAINT
.70(9,;()D#  
  # D 7# ;    
 4# 5A 3 
;I*B
3 ;7#;
B DC
C/AA#
KAC
R##D

+(.*(*.2( 4
 ,0*,'/
 , A4"%5,0*,'/
 .#,0*,'/
 .A4"5/
  4-/"5/
3$.);()45
5N


)7K 1
+(.*(*.2(040,0*,'/0, 
,0*,'//
3$.);()405/+0,*.,*7;O 0
70(9,;()455N

B D #
D
K,BH $$A
*70(9,;()F3F 
F0F/KL
.*(*.2(0
.70(9,;()45(7((,+( 45N
DROP A FOREIGN KEY CONSTRAINT
*B70(9,;() /
KL
.*(*.2(0
0370(9,;()7;O30N

SQL CHECK CONSTRAINTS
*+(+;+0,*.,* #A
B C+(+;
# /BCA 
C+(+;/ 
AB A 

CB $$A
+(+; F.#F
F3F*+(+; D
AB-L
+(.*(*.2(34
 ,0*,'/
 , A4"5,0*,'/
 7, A4"5/
 .#/
 +(+;4.#VP-5
5N

A3#AD
.# .#[-/
/
,(*,*038.'(4/QR/QR/!5N

7#+(+; B /
ML

CB $$A
+(+; F.#F
/#L
.*(*.2(3
.+(+;4.#VP-5

C#+(+; B /
KL
.*(*.2(3
.+0,*.,*+;O3.#+(+;4.#VP-.,
+PEE5N

DROP A CHECK CONSTRAINT
*B+(+; /#L
.*(*.2(3
03+(+;+;O3.#N

ABB+;O3.##B 
SQL DEFAULT CONSTRAINT
*(7.'*BAA
 *AA
A 
K$ $$A
*K(7.'*AF+F 
F3F
+(.*(*.2(34
 ,0*,'/
 , A4"5,0*,'/
 7, A4"5/
 .#/
 +A4"5(7.'*EE
5N
.3/ 

K$ $$A
*(7.'* F+F
/#L
.*(*.2(3
.*(.#(*(7.'*"%N

DROP A DEFAULT CONSTRAINT
*B(7.'* /KL
.*(*.2(3
.*(+03(7.'*N

.3/A
 A
7. SQL CREATE INDEX STATEMENT
+(.*( ,(6         M 
*MA 
KD*M/
HBBK@
 L'B#MD 
B#MM
B/M 
K#
$D:
MBAL
+(.*(,(6MO 
0,O 4 / "/5N
:%L
+  M   FMO F   F, F
 F3FL
+(.*(,(6MO 
34, 5
M  /
  B/B
 L
+(.*(,(6MOB 
0,34, /7, 5N
$D
KMBA

:(
+','(,(6MO 
O 4 / "/5N
 L * K  # M A  # I
 */ +D  K  # M 

DROP INDEX STATEMENT
*03,(6 M

.*(*.2(O 
03,(6MO N
8. SQL VIEWS STATEMENT
/AA<
 .A / 
*CAC  
 )/((/>0,
 AB
 # #
$E<:
+(.*(8(AO .
((+* / "/
70$O 
((N
 L.AB<<S*#
/#AE /A 
KA
 

+A /A
  A      #    

+(.*(8(+'*0$(O8(.
((+* /#
70$ N

The WITH CHECK OPTION
* <CCB& +(.*(8( 
B*HA*+(+;03*0, D
'3.*(,(*45A
C
      45/  '3.*(  ,(*

*#DM B# 
A+'*0$(O8(*+(+;03*0,
+(.*(8(+'*0$(O8(.
((+* /#
70$ 
((#,0*,'
*+(+;03*0,N

AA4+'*0$(O8(5)7771
B
DELETING ROWS INTO A VIEW
 A* 
BB'3.*(,(* BB((*(
 
:%A#.9(P"
((*(70$+'*0$(O8(
 ((#P"N

A/#P"
DROPPING VIEWS
A/ BA
#*K##A
L
038(AO N
R BB#B/A
./AA
BB#A
9. STORED PROCEDURE AND FUNCTIONS
.A $ BA  #  
3/8/*##/(AMB/
##A #
B$D
9.1. MySQL Stored Procedure
<7 & I
*   %   BB  K   
AKAA#/
KAA#/
DBBM'B
B BB
B A#A
9.1.1. Creating the Stored Procedure
:!  & 
($*(\\
+(.*(30+('(30+('(O,.$(45
2(9,
((+*+ O /+ O "/WWW
70$*O 
(,\\
($*(
/($*(B/C 
#      NN      
#       *  B 
A 
* M  K$/$D
</      B 
A$K

AB =A
  .A
BDBAK/
BD#M
DB
9.1.2. EXECUTION OF STORE PROCEDURE
(M    3  A  B  # 
$ % 9= (M   K  # 
CB
. #  B/       
  B*B
L

3AB /M
B# #]
4DL 7/ 003/ +.(/ 5   B  D
  K     B   B
B GK
9.1.3. DROP THE STORED PROCEDURE
BBB 
*#K
BL
03OBO 

*MBOB
CDBO 1MB
B 
0330+('(^7(6*_OBO N

BAD

9.1.4.STORED PROCEDURE PARAMETERS
    B  B   
B  B   D ,/ 0'*  ,3'* *
B  D3 ]M
K,$&$$
*CB B/
KL
^,`0'*`,3'*_3..$(*(O,.$(B^4#5_
&
B 3 #
B# B# 3*A
B AA#
B#A
3
:%! IN: +3C
A BB $#8

0BL
&
* A  0B 3   #  
3BA#
3
:%! OUT: BB
K 

0BL
* OKBB 
%9LDB # 
9LB
* B  B/  A   OK45  B 
# /C# BB/
BaO 


 ,0'*3 
:% !  INOUT (    H # 
 ##B

0BL
  B  K  K      
B  / 
BA

9.1.5. STORED PROCEDURE VARIABLES
        A/    
AA2A
    H  A   # 
M3
$,CE$$A
*AB/
KL
(+.( 8O  B4G5 ^(7.'*
OA_N
/
(+.( ?   D       
A 7  (+.( D  
AO 
B4G5 ?     C  A B
4DL,/8/5GC#
A
?#AAB
AB#A/
RA,'

'#$3/ 
A
$,,E$$A
0A/*#
AA L

*AA*#%
ABL

  A #  A  #  
BBI 4M5
R3DBA
.BL

E$$A&
8B  BC
B A, B
 
   A   A, 
 /      B     
/
9.2. CONDITIONAL STATEMENT
7 $
  +< 
$
$/+<  L K0C/K0
C0  K0C0K08   ##
 
9.2.1. IF-THEN STATEMENT
K0C MD
BCM7<*(,L
7*(,
 N
(,7N
/
7/DM 
K0CK*'(/
#M(,7
AK/D#
HQ BRQ2*RR
DBBQ9!7L8
./ #2*/
D

9.2.2. IF-THEN-ELSE STATEMENT
K0C0 K0C8M
DB
#D*ML
7+*(,N
 N
((
(< N
(,7N
##DH Q BR
    C    B  M
YHAZ
RBDBA
K

./A#AH QR/
BM#ABQH
AR
9.2.3. IF THEN ELSEIF ELSE STATEMENT
K0C0 K0C8M
DB K 1 
#KD#
CMD
*ML
7+*(,N
7< N
((7
((7< N
((
((< 
(,7N
,##B  BA/
##DH !DQ BR
      C   0! B 
MC  D
#M
RBDBA
K
*<D#MB 
A$8

RB/
* MB<<
 7<*(,/7<*(,<((/7<*(,<
((7<((
9.3. CASE STATEMENT
/##$/
A 7< 
+.(   D    b  
+.(      L % $  7
$
/R+.( #
3
9.3.1. Simple CASE Statement
* B+.( K BOA
OACK*M
 B+.( L
+.(OA
OA*(, 
W
^((< _
(,+.(N
OAKOA
M .C
+.(+.( 
* A     OA   K 
OA BA,D
DL
+.(OA
OA*(, 
W
^((< _
2(9,
(,N
(,+.(N
   ##     B  7 
 ##DH#
#+.( 
#7<((A+.( /
D#YZ
 AC
+.( ((D#M
RK3,
B+ L
./

,/RD/OAC
DM
./BM#YH
AZ
9.3.2. Searched CASE Statement
+.(   B+ 
 BAA
KA7  7
 *B   BM 4D#5/
+.( 
*ML
+.(OA
OA*(, 
WW
^((< _
(,+.(N
*  $   D  79  
(,C*'(M
B#*(,  79   
C$A 
##H   D#
M#
M
CMD
   #/   H    Q BR
Q2*R.C
MDQH
AR
R31DC
ABH Q BR
#2*RAD0B
 /RMBL
.    /  M   B   
3.#BQHAR
$EK$
7+.(MDBC
7+.( 
B/R BB
 B+.(  b
7  B##MB
7 M# BM
MB
#+.( / D
C
B
9.4. LOOP STATEMENT
$/& M 
 B
*M003 L
^#BL_B
 O
(,003^O_
*BM9B
 O B
  O* B$E
 C
*M003 (.8( L
^_L003
7+*(,
(.8(^_N
(,7N
(,003N
*$E MD1
B# ### M B
##0033B
A  %*BA 
 B L
* %
* A"
R B #K3
#0037<*(, *K
BA  %
RBD
./BA 
%
9.5. WHILE LOOP STATEMENT
(BMD*'(
*ML
^#BL_(0
 
(,(^O_
*( D##
C/M
         A  
 0(,(
##B  %#B
B

RBDBA
K

/BK
  %4M#%52A#A
B[%//B
  =
9.6. REPEAT LOOP STATEMENT
(3(.* M  
C 0(003
+*M(3(.*B L
(3(.*
 
',* 
(,(3(.*
/(3(.*003M D#
  *      M 
DB<B
RB   "%4M#"%5#
(3(.*BB

EDBB#

AB  "%#
BBB
9.7. CURSOR
$  3  #
 
B.
KB
#B
$</,</.A
MBL
'8 +O +'0 ((+*O 
*AN
/
*8 03(,+O 
B#03(, 
03(,G
+8  +0,*,'( .,( 70 ,0* 70',
4*  5
*    /    A K *
CA

.8 7(*+ ,*0AO
2#7(*+ /AMB
 AM
/8 +O 
2# +0(/ A  
 
R B #/
###     
* B /##ACAB

RDB#B

B/ACMB 
  O#
B
9.8. ERROR HANDLING
$(0.,,9(
BAB/
  A  B    *  / $ 
BA#C
4#MBBC5
*/#ML
(+.(.,(70 N
   /   $  M 
 M D

.B#AL
LMD#
DL  M   #  D/  
 
7 C! 
*#AAK
MN
(+.(+0,*,'(.,(70(6+((3*0,
(*OAP
7 C! D
*#DBAB/
 #MD
2(9,(,D
B/ B 

RM BN
# B#B
4M5
 A    ( B  #A
BDO( BB D4( B5R
B
AB/B
 #
##BA

  #A#  #      
#    /    
B  2 #  /   B   #
AAB

A
 B   .  A   +0,*,'(
AM B,RM B
(6*#B##
  BMB
M B/  ##B 
#M/M
 #  D/      
 

#L

2#(6*
B #A


B(6*/H#A
 #A
9.8.1. ERROR CONDITIONS WITH MySQL SIGNAL/ RESIGNAL
STATEMENT
,$$
# #
#BBA
# #A
*M9,. L
9,.*.*(`O N
(*O O O PA
(*O O O PA"
9,.D*.*(A 
 # (+.( +0,*0, *
O O O   $(.9(O*(6*/
$)O(0/+'0O,.$(
RB /
##DO 
#A #Y
 Z
,LJ./666J #J$JA   
<CMB
0BL
./ ##A#Y
 Z
,$$
(9,. *.*($(,*     #  
#       9,.     
M/MBL
 A(9,. 
 /       
Y(9,.AZ
    (9,.  /
A*.*(A
#BA 
  /       A  G   
 #YAGZ
R    B  D  B/  
A G
/   A#    G/  R # 
 #YAG@ GZ
9.9. STORED FUNCTION
*0(7',+*0,BBB# 
B  
/
9.9.1. DECLARING STORED FUNCTION
($*(\\
+(.*(7',+*0,O 4
 B /
 B "/W5
(*',B
^,0*_(*($,*+
2(9,
 
(,\\
($*(N
* M          
B# ###7
+(.*(7',+*0,O 4BB 5
(*', B      A@   *
B(*(,$,*+0,0,<(*($,*+
$,0,<(*($,*+
,   ##       
##CHO  #
BBAO 

RD   B  #   ((+*
 
 A      H  BB# 
HRDB

.   /    #A#  H  
BA
9.9.2. DROP FUNCTION
*037',+*0,B
 2#ML
037',+*0,O N
7',+*0,M/DL
037',+*0,7(6*O N
RM BA7/
##BH45
,/RD B
## #M*

10. TRIGGERS
*##   B#  AD   
BA/B
BB/C##
/ AD

*B*99(L
'8  )0#0L   A     
/B
*8 0#0L   M  

 LBB )0#0

$# !
BAD#
##
 
10.1. CREATING TRIGGERS
+(.*(*99( ##*
M#L
+(.*(*99(##O 
c2(70(`.7*(dc,(*`((*(`'3.*(d
0,O 70(.+0
##ON
/
*C###
##O 
 D##AD
 C
*B,(*/((*(0
'3.*(O 
R###M B,/
## ( B.C
B DN
+ /
## Y BZ0Y BZ
/##B ##B
B#Y( BZ
R  ## #   B B 
 B./## 
YOO BOBZ
,/R##BA B/
RD( BD
./## AD
( BB

10.2. DROP TRIGGER
**99(03*99( /
## *M
L
03*99(^7(6*_##O N
/
7/D##O M
B##
*##/##O 
03*99(
0
03*99(##O N
##D#M


* ## ! 9 9% 9%   
 
10.3. BEFORE INSERT TRIGGER
*## C
*M##L
+(.*(*99(##O 
 2(70(,(*
 0,O 70(.+0
##ON
R#M B#
  O
,/R  
 ##

### 
  D
  
*##B#
  R###
A

AA 
2AAD 
R  D
 
*##AD
 A
 B 
 
10.4. AFTER INSERT TRIGGER
*## C
*M##L
+(.*(*99(##O 
 .7*(,(*
 0,O 70(.+0
 ##O
R###M BN
+ 

+8
,/##!99
## O
B
R##
A  
 N
./
  ##/
AD #RD

.A ##/AD
 . #
# /BOO
10.5. BEFORE UPDATE TRIGGER
*2(70('3.*(*99(AD 
BA
##
+(.*(*99(##O 
2(70('3.*(
0,O 70(.+0
##O
R#M BN
+N
N
+#2(70('3.*(*99(/##
#K#&< 
BA
*## ADCB#
A
RBAN
ABAKP
C#A #/
N
RBK A&< 
#K%
.  A   K  &<  # 
BA/R# #YK
& #KZ
10.6. AFTER UPDATE TRIGGER
*.*('3.*(*99(AD B#
A*M.7*(B
##L
+(.*(*99(##O 
.7*('3.*(
0,O 70(.+0
##O
RB##M BN
##C
978RO#
,/R.7*('3.*(*99(N
*OBO## AD
B#
'B#K /P
RD97N
.AB 
O#
10.7. BEFORE DELETE TRIGGER
*2(70(((*(*99(C 
A*M##
L
*99(##O 
 2(70(((*(
 0,O 70(.+0
##O
RO
N
+O
N
,RB/
##2##A
O
R N
,/DOD

./2(70(((*(*99(
 ADA

10.8. AFTER DELETE TRIGGER
.7*(((*(*99(AD #
 A     * M  .7*( ((*(
*99(L
+(.*(*99(##O 
 .7*(((*(
 0,O 70(.+0
##ON
+N

+   / 
#ON
,/RAO
# /#'$45
            
O
 -%%%%
,/R.7*(((*(*99(N
###B
O# 
R      BO P %""  
N
+DON
.A%%%%/
  